from sqlalchemy import select, update, delete
from sqlalchemy import select, update, delete
from sqlalchemy.ext.asyncio import AsyncSession

from module_kylin.entity.do.future_strategy_instance_do import KylinFutureStrategyInstance
from module_kylin.entity.vo.future_strategy_instance_vo import (
    FutureStrategyInstanceModel,
    FutureStrategyInstancePageQueryModel,
)
from utils.page_util import PageUtil


class FutureStrategyInstanceDao:
    """
    期货策略实例数据库操作层
    """

    @classmethod
    async def get_strategy_instance_detail_by_instance(cls, db: AsyncSession, instance_name: str):
        """
        根据策略实例名获取策略实例信息

        :param db: orm对象
        :param instance_name: 策略实例名
        :return: 策略实例信息对象
        """
        instance_info = (
            await db.execute(select(KylinFutureStrategyInstance).where(KylinFutureStrategyInstance.instance == instance_name))
        ).scalars().first()

        return instance_info
    
    @classmethod
    async def get_strategy_instance_detail_by_id(cls, db: AsyncSession, instance_id: int):
        """
        根据策略实例id获取策略实例信息

        :param db: orm对象
        :param instance_id: 策略实例id
        :return: 策略实例信息对象
        """
        instance_info = (
            await db.execute(select(KylinFutureStrategyInstance).where(KylinFutureStrategyInstance.id == instance_id))
        ).scalars().first()

        return instance_info

    @classmethod
    async def get_strategy_instance_detail_by_info(cls, db: AsyncSession, instance: FutureStrategyInstanceModel):
        """
        根据参数配置参数获取参数配置信息

        :param db: orm对象
        :param instance: 参数配置参数对象
        :return: 参数配置信息对象
        """
        query = select(KylinFutureStrategyInstance)
        
        if instance.server:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.server.ilike(f'%{instance.server}%'))
        if instance.group:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.group.ilike(f'%{instance.group}%'))
        if instance.instance:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.instance.ilike(f'%{instance.instance}%'))
        if instance.class_name:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.class_name.ilike(f'%{instance.class_name}%'))
        if instance.vt_symbol:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.vt_symbol.ilike(f'%{instance.vt_symbol}%'))
        
        instance_info = (await db.execute(query)).scalars().first()
        return instance_info

    @classmethod
    async def get_strategy_instance_list(cls, db: AsyncSession, query_model: FutureStrategyInstancePageQueryModel):
        """
        获取策略实例列表

        :param db: orm对象
        :param query_model: 查询参数对象
        :return: 策略实例列表
        """
        query = select(KylinFutureStrategyInstance)
        
        if query_model.server:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.server.ilike(f'%{query_model.server}%'))
        if query_model.group:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.group.ilike(f'%{query_model.group}%'))
        if query_model.instance:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.instance.ilike(f'%{query_model.instance}%'))
        if query_model.class_name:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.class_name.ilike(f'%{query_model.class_name}%'))
        if query_model.vt_symbol:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinFutureStrategyInstance.vt_symbol.ilike(f'%{query_model.vt_symbol}%'))
        
        # 处理排序参数
        if hasattr(query_model, 'sort_field') and query_model.sort_field:
            # 字段映射，将前端驼峰命名转换为后端下划线命名
            field_mapping = {
                'instance': KylinFutureStrategyInstance.instance,
                'server': KylinFutureStrategyInstance.server,
                'group': KylinFutureStrategyInstance.group,
                'className': KylinFutureStrategyInstance.class_name,
                'vtSymbol': KylinFutureStrategyInstance.vt_symbol
            }
            
            # 获取排序字段
            sort_field = field_mapping.get(query_model.sort_field)
            if sort_field:
                # 确定排序方向
                if hasattr(query_model, 'sort_order') and query_model.sort_order and query_model.sort_order.lower() == 'desc':
                    query = query.order_by(sort_field.desc())
                else:
                    query = query.order_by(sort_field.asc())
        else:
            # 默认按实例名升序排序
            query = query.order_by(KylinFutureStrategyInstance.instance.asc())
        
        # 构建分页查询，添加is_page=True确保开启分页
        strategy_instance_page = await PageUtil.paginate(db, query, query_model.page_num, query_model.page_size, is_page=True)
        
        return strategy_instance_page

    @classmethod
    async def add_strategy_instance(cls, db: AsyncSession, instance: FutureStrategyInstanceModel):
        """
        添加策略实例

        :param db: orm对象
        :param instance: 策略实例对象
        :return: 操作结果
        """
        db_instance = KylinFutureStrategyInstance(
            server=instance.server,
            group=instance.group,
            instance=instance.instance,
            class_name=instance.class_name,
            vt_symbol=instance.vt_symbol
        )
        db.add(db_instance)
        await db.flush()
        return db_instance

    @classmethod
    async def update_strategy_instance(cls, db: AsyncSession, instance_name: str, instance: FutureStrategyInstanceModel):
        """
        更新策略实例

        :param db: orm对象
        :param instance_name: 策略实例名
        :param instance: 策略实例对象
        :return: 操作结果
        """
        update_data = {}
        if instance.server is not None:
            update_data[KylinFutureStrategyInstance.server] = instance.server
        if instance.group is not None:
            update_data[KylinFutureStrategyInstance.group] = instance.group
        if instance.class_name is not None:
            update_data[KylinFutureStrategyInstance.class_name] = instance.class_name
        if instance.vt_symbol is not None:
            update_data[KylinFutureStrategyInstance.vt_symbol] = instance.vt_symbol
        
        await db.execute(
            update(KylinFutureStrategyInstance)
            .where(KylinFutureStrategyInstance.instance == instance_name)
            .values(update_data)
        )

    @classmethod
    async def delete_strategy_instance(cls, db: AsyncSession, instance_name: str):
        """
        删除策略实例

        :param db: orm对象
        :param instance_name: 策略实例名
        :return: 操作结果
        """
        await db.execute(
            delete(KylinFutureStrategyInstance)
            .where(KylinFutureStrategyInstance.instance == instance_name)
        )

    @classmethod
    async def batch_delete_strategy_instance(cls, db: AsyncSession, instance_names: list[str]):
        """
        批量删除策略实例

        :param db: orm对象
        :param instance_names: 策略实例名列表
        :return: 操作结果
        """
        await db.execute(
            delete(KylinFutureStrategyInstance)
            .where(KylinFutureStrategyInstance.instance.in_(instance_names))
        )
